package it.tcm.TeacherClassMate.facade;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import it.tcm.TeacherClassMate.beans.DisequationBean;
import it.tcm.TeacherClassMate.beans.EquationBean;
import it.tcm.TeacherClassMate.dao.DisequationDao;
import it.tcm.TeacherClassMate.persistent.HibernateUtil;
import it.tcm.TeacherClassMate.util.Util;


public class DisequationManageBase {

	 	private Logger logger = Logger.getLogger(this.getClass().getName());   
	    private DisequationDao dDao = new DisequationDao();
	  
	    public DisequationBean getDisequationById(Integer id) {
		    Session session = null;
		    DisequationBean disequation = null;
			try {
				session = HibernateUtil.createNewSessionAndTransaction();
				dDao.setSession(session);
				disequation = dDao.findById(id, false);
			    HibernateUtil.commitTransactionAndCloseSession(session);
			} catch (Exception e) {
				HibernateUtil.rollbackTransaction(session);
			    logger.error("Error in reading disequation "  + id.toString(), e);
			} finally {
			}
			return disequation;
	    }
	    
	   
	    
	    public Integer save(DisequationBean diseq)
	    {
	  	  Session session = null;
	  	  try 
	  	  {
	  		session = HibernateUtil.createNewSessionAndTransaction();
	  		dDao.setSession(session);
	  		dDao.attachDirty(diseq);
	  		HibernateUtil.commitTransactionAndCloseSession(session);
	  	  } 
	  	  catch (Exception e) 
	  	  {
	  		  logger.error("Errore in save disequation ", e);
	  		  HibernateUtil.rollbackTransaction(session);
	  	  } 	   	  
	  	  return diseq.getId();
	    }
	    
	    
	    public boolean delete(DisequationBean diseq) {  
	  	  Session session = null;
	  	  try 
	  	  {
	  		session = HibernateUtil.createNewSessionAndTransaction();
	  		dDao.setSession(session);
	  		dDao.delete(diseq);
	  		HibernateUtil.commitTransactionAndCloseSession(session);
	  	  } 
	  	  catch (Exception e) 
	  	  {
	  		  logger.error("Errore in delete disequation ", e);
	  		  HibernateUtil.rollbackTransaction(session);
	  	  } 	   	  
	  	  return true;
	    }
	    
	    public List<DisequationBean> getAll() {
		    Session session = null;
		    List<DisequationBean> dList = null;
			try {
				session = HibernateUtil.createNewSessionAndTransaction();
				dDao.setSession(session);
				dList = dDao.findAll();
			    HibernateUtil.commitTransactionAndCloseSession(session);
			} catch (Exception e) {
				HibernateUtil.rollbackTransaction(session);
			    logger.error("Errore in reading all disequations ", e);
			} finally {
			}
			return dList;
	    }
	    
	    
	    public Integer getTotalNumberOfDisequation() {
	    	Session session = null;
	    	Integer i = null;
			try {				
				session = HibernateUtil.createNewSessionAndTransaction();				   
				String SQL_QUERY = "";
		    	SQL_QUERY = SQL_QUERY + "select count(*) ";
		    	SQL_QUERY = SQL_QUERY + "from DISEQUATION e ";    	    	    	       
	    	    Query query  = session.createSQLQuery(SQL_QUERY);
  	    
	    	    BigInteger valore = (BigInteger)query.uniqueResult();
	    	    
	    	    i = valore.intValue();
	    	    
	    	    HibernateUtil.commitTransactionAndCloseSession(session);				 
			    
			} catch (Exception e) {
				HibernateUtil.rollbackTransaction(session);
			    logger.error("Errore in getTotalNumberOfDisequation", e);
			}
			return i;
	    	
	    }
	   
	    
	    @SuppressWarnings({ "unchecked", "rawtypes" })
		public List getDisequationByCriteria(List<ArrayList>  criteriaArray) {
		    Session session = null;
			List<DisequationBean> list = null;
			ArrayList<String> arrayColumns = null;
    		ArrayList<String> arrayOperator = null;
    		ArrayList<String> arrayDataType = null;
    		ArrayList<String> arrayValues = null;	
			try {   
				 session = HibernateUtil.createNewSessionAndTransaction();
				 logger.debug("Criteria array size: " + criteriaArray.size());
			    	if (criteriaArray.size() == 4){ //there are 4 arrays in the list
			    		Iterator it1 = criteriaArray.iterator();
				    	arrayColumns = (ArrayList<String>)it1.next();
				    	arrayOperator = (ArrayList<String>)it1.next();
				    	arrayDataType = (ArrayList<String>)it1.next();
				    	arrayValues = (ArrayList<String>)it1.next();
			    	} else {
			    		throw new Exception("Criteria array with wrong size.");
			    	}
			    	
			    	int i = 0 ;
			    	String SQL_WHERE_CLAUSOLE = "where ";
			    	for (i=0; i<arrayColumns.size(); i++){
			    		String column = arrayColumns.get(i).toString();
			    		String operator = arrayOperator.get(i).toString();
			    		String dataType = arrayDataType.get(i).toString();
			    		String value = arrayValues.get(i).toString().trim();
			    		logger.debug(i+1 + ". criteria --> " + column + " " + operator + " " + value + " [" + dataType + "]");	    	
			    		String sqlOperator = "";
			    		if (operator.equalsIgnoreCase(Util.SQL_OPERATOR_EQ) ){
			    			sqlOperator = "=";
			    		}else if (operator.equalsIgnoreCase(Util.SQL_OPERATOR_GT) ){
			    			sqlOperator = ">";
			    		}else if (operator.equalsIgnoreCase(Util.SQL_OPERATOR_GTE) ){
			    			sqlOperator = ">=";
			    		}else if (operator.equalsIgnoreCase(Util.SQL_OPERATOR_LIKE)){
			    			sqlOperator = "like"; 
			    			value = "%" + value + "%";
			    		}else if (operator.equalsIgnoreCase(Util.SQL_OPERATOR_LIKE_UPPER)){
			    			sqlOperator = "like"; 
			    			value = "%" + value + "%";
			    			column = "upper(" + column + ")";
			    		}else if (operator.equalsIgnoreCase(Util.SQL_OPERATOR_LT)){
			    			sqlOperator = "<";
			    		}else if (operator.equalsIgnoreCase(Util.SQL_OPERATOR_LTE) ){
			    			sqlOperator = "<=";
			    		}	
			    		if (dataType.equalsIgnoreCase(Util.SQL_TYPE_STRING)){			    			
			    			SQL_WHERE_CLAUSOLE = SQL_WHERE_CLAUSOLE + column + " " + sqlOperator + " '" + value + "' and ";	
			    		}else if (dataType.equalsIgnoreCase(Util.SQL_TYPE_INTEGER)){			
			    			SQL_WHERE_CLAUSOLE = SQL_WHERE_CLAUSOLE + column + " " + sqlOperator + " " + value + " and ";			
			    		}else if (dataType.equalsIgnoreCase(Util.SQL_TYPE_DATE)){			    			
			    			SimpleDateFormat dateFormatIn = new SimpleDateFormat(Util.DATE_FORMAT);
			    			SimpleDateFormat dateFormatOra = new SimpleDateFormat(Util.ORA_DATE_FORMAT);
			    			Date dateValue = dateFormatIn.parse(value);
			    			StringBuilder dateValueOra = new StringBuilder(dateFormatOra.format(dateValue));
			    			SQL_WHERE_CLAUSOLE = SQL_WHERE_CLAUSOLE + column + " " + sqlOperator + " '" + dateValueOra + "' and "   ;
			
			    		}
			    	}
			    	logger.debug(SQL_WHERE_CLAUSOLE);
			    	SQL_WHERE_CLAUSOLE = SQL_WHERE_CLAUSOLE.substring(0, SQL_WHERE_CLAUSOLE.length()-4);
			    	logger.debug(SQL_WHERE_CLAUSOLE);
			    	String SQL_QUERY = "";
				    SQL_QUERY = SQL_QUERY + "select * ";
				    SQL_QUERY = SQL_QUERY + "from DISEQUATION ";
		    	    SQL_QUERY = SQL_QUERY + SQL_WHERE_CLAUSOLE; 
		    	    logger.debug(SQL_QUERY);
		    	    Query query = session.createSQLQuery(SQL_QUERY).addEntity(EquationBean.class);   
		    	    list = query.list();    
		    	    HibernateUtil.commitTransactionAndCloseSession(session);
			} catch (Exception e) {
				HibernateUtil.rollbackTransaction(session);
			    logger.error("Errore in ricerca disequazione", e);
			} finally {
			}
			return list;
		    }
	    

}
